Skip to main content

SQL Statements

Categories​

1. Data Definition Language (DDL)​

DDL statements define and modify the structure of a database.

  • CREATE
  • ALTER
  • DROP

2. Data Manipulation Language (DML)​

DML statements manipulate data stored in tables.

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

3. Data Control Language (DCL)​

DCL manages access to data.

  • GRANT
  • REVOKE

CRUD​

Creation​

INSERT INTO table_name (columns) VALUES (values);
  • Column and value order should be same
  • It's not mandatory to follow the order of column as same as the order of column during definition, just maintain that it's follow order in sql command
  • Any value that goes into a VARCHAR, CHAR, DATE or TEXT column has single quotes
  • You can skip column names but values order should be same as column and can't leave any column
  • For multiple insertion separate all tuple with comma
  • You can insert data only in specified columns by skipping other column name in sql command

READ​

SELECT */column_name FROM table_name;
  • * return all column

UPDATE​

  1. Updating a Single Column for a Single Record:

    You can use a simple UPDATE statement to modify a single column for a single record:

    UPDATE table_name
    SET column_name = value
    WHERE column_name = value;

  2. Updating Multiple Columns for a Single Record:

    You can update multiple columns at once by separating each column-value pair with a comma:

    UPDATE table_name
    SET column_name_1 = value_1, column_name_2 = value_2
    WHERE condition;

  3. Updating Multiple Records with a Single Column Using CASE:

    If you want to update multiple records conditionally, based on specific criteria, you can use CASE within the UPDATE statement. Note that the CASE expression is used to modify a single column based on conditions.

    UPDATE table_name
    SET column_name = CASE
                        WHEN condition_1 THEN new_value_1
                        WHEN condition_2 THEN new_value_2
                        ELSE column_name
                      END
    WHERE some_condition;

    This CASE statement allows you to conditionally assign new values to a column based on multiple conditions, but remember, only one column can be updated in this case.

DELETE​

DELETE FROM table_name WHERE column_name=value;
  • WHERE is necessary otherwise all records will be delete

WHERE​

Apply the most restrictive conditions first in the WHERE clause to minimize the dataset as early as possible.

It is used in SQL to filter records from a table based on specific conditions.

WHERE column_name operator 'value';

Operators​

1. Comparison Operators

  • =: Equal to
  • != or <>: Not equal to
  • >: Greater than
  • <: Less than
  • >=: Greater than or equal to
  • <=: Less than or equal to

2. Logical Operators

  • AND: Combines multiple conditions; all must be true.
  • OR: Combines multiple conditions; at least one must be true.
  • NOT: Reverses the result of a condition.

3. Wildcard Operators

  • LIKE: Matches patterns (e.g., % for any sequence of characters).
  • IN: Matches a list of values.
  • BETWEEN: Checks if a value is within a range.

4. NULL Check

  • IS NULL: Checks if a value is NULL.
  • IS NOT NULL: Checks if a value is not NULL.

Sorting​

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

LIMIT​

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;
  • row_count: The maximum number of rows to return.
  • offset (optional): The number of rows to skip before starting to return rows.

Aliases​

It provide temporary names for columns or tables in a query to make them more readable or easier to reference. These aliases exist only for the duration of the query and do not affect the actual database schema.

SELECT column_name AS alias_column_name
FROM table_name AS alias_table_name;

Just put the alias name after actual name by following a AS.